<?php
declare (strict_types=1);

namespace app\db;

use app\util\Tool;
use think\Config;
use think\facade\Db;
use think\Model;


class BigDb
{
    public function comDbfindtheme($uniacid, $id)
    {

        $tool = new Tool();
        //ims_water_salegroup3_theme
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime,finishsum,groupsum,fanssum  from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
            $begintime = $sqldata[0]['begintime'];
            $begintime = time();
            $endtime = $sqldata[0]['endtime'];
            //countdown  两个时间戳
            $datestr = $tool->timediff($begintime, $endtime);
            //$arr['datestr'] = $datestr;
            $arr['vdata']['datestr'] = $datestr;;
        } else {
            $arr['vdata'] = null;
        }

        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        $arr['ask'] = "theme表";
        $arr['remark'] = "合并DB";
        return $arr;

    }


    /**
     *
     *  1活动标题
     * ims_water_salegroup3_theme表格中按照uniacid和id检索，获取title一项目值
     */
    public function activitietitle($uniacid, $id)
    {
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);


        $arr['sql'] = $actionsql;
        $arr['sqldata'] = $sqldata;
        $arr['vdata'] = $sqldata[0];
        $arr['ask'] = "活动标题";
        $arr['remark'] = "活动标题";
        return $arr;
    }

    //ordersum
    public function ordersum($uniacid, $id)
    {

        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum from ims_water_salegroup3_theme  where uniacid = neeedu and  uid = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "订单总数";
        $arr['remark'] = "订单总数";
        return $arr;
    }

    /**
     * 准客数量
     * 备注，因为是带 status查询 所以暂时还合并不了
     * @return mixed
     */
    public function zhuncus($uniacid, $themeid)
    {

        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and `status` = 0 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "准客数量";
        $arr['remark'] = "准客数量";
        return $arr;
    }

    public function ut($uniacid, $themeid)
    {

        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and `status` = 0 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "ut";
        $arr['remark'] = "";
        return $arr;
    }

    //已支付 payon 参数
    public function payon($uniacid, $themeid)
    {

        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and  (`status` = 1 or `status` = 2 )  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "ut";
        $arr['remark'] = "已支付";
        return $arr;
    }


    //newcus
    public function newoldcus($uniacid, $themeid, $newold)
    {
        //str_replace need开头的替换成入参
        $neednewold = 'neednewold';
        $needu = 'neeedu';
        $needt = 'needt';
        //$orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and `status` = 0 ";
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and  (`status` = 1 or `status` = 2 ) and  `option` LIKE CONCAT('%', 'neednewold', '%')";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($neednewold, $newold, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);

        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "newoldcus";
        $arr['remark'] = "";
        return $arr;
    }


    /**
     *
     * ims_water_salegroup3_theme表格中按照uniacid和id检索，获取title一项目值
     */
    public function fanssum($uniacid, $id)
    {

        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,fanssum from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "访客数量";
        $arr['remark'] = "访客数量";
        return $arr;
    }


    //countdown
    public function countdown($uniacid, $id)
    {
        //https://blog.csdn.net/weixin_34082177/article/details/94726075
        $tool = new Tool();
        //ims_water_salegroup3_theme
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
            //$begintime = $sqldata[0]['begintime'];
            $begintime = time();
            $endtime = $sqldata[0]['endtime'];
            //countdown  两个时间戳
            $datestr = $tool->timediff($begintime, $endtime);
            $arr['datestr'] = $datestr;
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "订单总数";
        $arr['remark'] = "订单总数";
        return $arr;
    }

    //fullgourp 满团数 总团数 ui
    public function fullgourp($uniacid, $id)
    {
        //https://blog.csdn.net/weixin_34082177/article/details/94726075
        $tool = new Tool();
        //ims_water_salegroup3_theme
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime,finishsum,groupsum from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "满团与总团";
        $arr['remark'] = "满团与总团";
        return $arr;
    }

    public function ui($uniacid, $id)
    {

        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime,finishsum,groupsum from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = array();
        }
        $arr['sql'] = $actionsql;

        $arr['asktable'] = "ims_water_salegroup3_theme";
        $arr['remark'] = "暂时没有备注";
        return $arr;
    }





    //worker - u t
    // 员工有多少员工就抓多少员工
    public function worker($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            //这个员工排行，因此最好是10条，但是有可能是0-1条
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "worker 表";
        $arr['remark'] = "";
        return $arr;
    }

    //测试接口
    //fan表  拉新数 - u t
    public function fans($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,openid,count(id) as qiu_count  from ims_water_salegroup3_fans where uniacid = 1 GROUP BY  openid  order by qiu_count Desc limit 10";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            //注意这个结果集是可能多条;且大部分情况是应该是10
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "worker 表";
        $arr['remark'] = "";
        return $arr;
    }


    //员工排行榜
    //workertop
    public function workertop($uniacid, $themeid)
    {
        //抓所有员工个数 -- ims_water_salegroup3_worker
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,openid,username,groups from ims_water_salegroup3_worker where `status` = 1 and  uniacid = neeedu and  themeid = needt  group by openid";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $worker_length = count($sqldata); //所有人 比如100个; 那么在record里很有可能 每人10个订单; 差不多有1000条数据 ；但是sql只要查 计数 例如A号员工 10个直推订单
        $instr = "";
        $countlength = count($sqldata);
        $arr['length_worker'] = $worker_length;
        if ($countlength > 0) {
            //$arr['sqldata'] = $sqldata;

            for ($i = 0; $i < $worker_length; $i++) {
                $instr .= "'" . $sqldata[$i]['openid'] . "'" . ',';
            }
            $instr = substr($instr, 0, -1);
            $arr['instr'] = $instr;

            //在 record 里把所有直推订单搜处理
            $sql_zhi_record = " select id,openid,fopenid,count(id) as zhi_count  from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt and (`status` = 1 or `status` = 2 ) and  (fopenid is not null  and fopenid <>'')  and fopenid in ($instr) GROUP BY  fopenid  order by zhi_count ";
            $sql_zhi_record = str_ireplace($needu, $uniacid, $sql_zhi_record);
            $sql_zhi_record = str_ireplace($needt, $themeid, $sql_zhi_record);

            $sql_zhi_record_sqldata = \think\facade\Db::query($sql_zhi_record);
            $arr['sql_zhi_record_sqldata'] = $sql_zhi_record_sqldata;
            $length_zhi = count($sql_zhi_record_sqldata);
            $arr['length_zhi'] = $length_zhi;


            //在 record 里把所有溯源订单搜出来
            $sql_su_record = " select id,openid,fopenid,count(id) as su_count  from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt and (`status` = 1 or `status` = 2 ) and (sourceid is not null and sourceid <>'') and sourceid in ($instr) GROUP BY  sourceid  order by su_count ";
            $sql_su_record = str_ireplace($needu, $uniacid, $sql_su_record);
            $sql_su_record = str_ireplace($needt, $themeid, $sql_su_record);

            $sql_su_record_sqldata = \think\facade\Db::query($sql_su_record);
            $arr['sql_su_record_sqldata'] = $sql_su_record_sqldata;
            $length_su = count($sql_su_record_sqldata);
            $arr['length_su'] = $length_su;

            //在 ims_water_salegroup3_fans 表的拉新数
            $sql_fan = "select id,openid,sourceid,count(id) as laxin_count  from ims_water_salegroup3_fans where uniacid = neeedu and themeid = needt and  openid  in ($instr) and (sourceid is not null and sourceid <>'') GROUP BY  openid  order by laxin_count Desc limit 10";
            $sql_fan = str_ireplace($needu, $uniacid, $sql_fan);
            $sql_fan = str_ireplace($needt, $themeid, $sql_fan);
            $sql_fan_sqldata = \think\facade\Db::query($sql_fan);
            $arr['sql_fan_sqldata'] = $sql_fan_sqldata;
            $length_fan = count($sql_fan_sqldata);
            $arr['length_fan'] = $length_fan;

            for ($i = 0; $i < $worker_length; $i++) {
                //直推订单
                for ($j = 0; $j < $length_zhi; $j++) {
                    if ($sqldata[$i]['openid'] == $sql_zhi_record_sqldata[$j]['fopenid']) {
                        $sqldata[$i]['zhi_record_num'] = $sql_zhi_record_sqldata[$j]['zhi_count'];
                    }
                }


                for ($j = 0; $j < $length_su; $j++) {
                    if ($sqldata[$i]['openid'] == $sql_su_record_sqldata[$j]['fopenid']) {
                        $sqldata[$i]['su_record_num'] = $sql_su_record_sqldata[$j]['su_count'];
                    }
                }

//                for ($j = 0; $j < $length_fan; $j++) {
//                    if ($sqldata[$i]['openid'] == $sql_fan_sqldata[$j]['openid']) {
//                        $sqldata[$i]['worker_fan_num'] = $sql_fan_sqldata[$j]['laxin_count'];
//                    }
//                }

            }
            for ($i = 0; $i < $worker_length; $i++) {
                if (!isset($sqldata[$i]['zhi_record_num'])) {
                    $sqldata[$i]['zhi_record_num']= 0;
                }
                if (!isset($sqldata[$i]['su_record_num'])) {
                    $sqldata[$i]['su_record_num']= 0;
                }
            }



            //再次排序
            usort($sqldata, function ($a, $b) {
                if(isset($b['zhi_record_num']) && isset($a['zhi_record_num']) ){
                    return $b['zhi_record_num'] <=> $a['zhi_record_num']; // 使用 PHP 7 的太空船操作符来进行比较
                }

            });

//            usort($sqldata, function ($a, $b) {
//                if(isset($b['su_record_num']) && isset($a['su_record_num']) ) {
//                    return $b['su_record_num'] <=> $a['su_record_num']; // 使用 PHP 7 的太空船操作符来进行比较
//                }
//            });
            $arr['sqldata'] = $sqldata;
            //处理下 “从0 开始取2个”
            $topdata = array();
            $qie = 3; //员工排行是取前3
            if ($countlength > $qie) {
                //想获取前2个为一组
                $topdata["one"] = array_slice($sqldata, 0, $qie);
                $topdata["two"] = array_slice($sqldata, $qie, count($sqldata) - $qie);
            } else {
                $topdata["one"] = $sqldata;
                $topdata['备足']= '小余3';
            }
            //$arr['sqldata'] = $sqldata;
            $arr['topdata'] = $topdata;
            $isDebug = \think\facade\App::isDebug();
            if ($isDebug) {
                $arr['sqlaction1'] = $actionsql;
                $arr['sqlaction2'] = $sql_zhi_record;
                $arr['sqlaction3'] = $sql_su_record;
                $arr['sqlaction4'] = $sql_fan;
            }

        } else {
            $arr['vdata'] = null;
        }
        return $arr;
    }

    //grouptop  团队排行
    //分析后sql语句不能共用一个是by openid 一个是by groups
    //方法可以共用 例如 xxxxx by （[openid|groups]）
    public function grouptop($uniacid, $themeid)
    {
        //找出所有groups 例如 大卫美术
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,openid,username,groups  from ims_water_salegroup3_worker where `status` = 1 and  uniacid = neeedu and  themeid = needt and (groups is not null and groups <> '') group by groups";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $group_length = count($sqldata); //这里主要查所有的团队
        $instr = "";
        //团队的个数大于0 才去查订单表 才有意义
        $countlength = count($sqldata);
        $arr['lenth_sql'] = $countlength;
        if ($countlength > 0) {
            $arr['group_length'] = $group_length; //团队个数
            for ($i = 0; $i < $group_length; $i++) {
                $instr .= "'" . $sqldata[$i]['groups'] . "'" . ',';
            }
            $instr = substr($instr, 0, -1);
            //$arr['instr'] = $instr;
            //instr 查找条件

            //在 record 里把所有直推订单 按groups 的条件找出来
            $sql_g_zhi_record = " select id,openid,fopenid,sourceid,groups,sourceid,count(id) as g_zhi_count  from ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt and (`status` = 1 or `status` = 2 )  and  (fopenid is not null  and fopenid <>'') and (groups is not null  and groups <>'') and groups in ($instr) GROUP BY  groups  order by g_zhi_count DESC";
            $sql_g_zhi_record = str_ireplace($needu, $uniacid, $sql_g_zhi_record);
            $sql_g_zhi_record = str_ireplace($needt, $themeid, $sql_g_zhi_record);
            $sql_g_zhi_record_sqldata = \think\facade\Db::query($sql_g_zhi_record);
            //$arr['sql_g_zhi_record_sqldata'] = $sql_g_zhi_record_sqldata;
            $length_g_zhi = count($sql_g_zhi_record_sqldata);
            //$arr['length_g_zhi'] = $length_g_zhi;

            //在 record 里把所有溯源订单搜出来 按groups 的条件找出来
            $sql_g_su_record = " select id,openid,fopenid,sourceid,groups,sourceid,count(id) as g_su_count  from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt and  (`status` = 1 or `status` = 2 ) and  (sourceid is not null and sourceid <>'')  and  (groups is not null  and groups <>'') and groups in ($instr) GROUP BY  groups  order by g_su_count DESC ";
            $sql_g_su_record = str_ireplace($needu, $uniacid, $sql_g_su_record);
            $sql_g_su_record = str_ireplace($needt, $themeid, $sql_g_su_record);
            $sql_g_su_record_sqldata = \think\facade\Db::query($sql_g_su_record);
            //$arr['sql_su_record_sqldata'] = $sql_g_su_record_sqldata;
            $length_g_su = count($sql_g_su_record_sqldata);
            //$arr['length_g_su'] = $length_g_su;

            //在 ims_water_salegroup3_fans 表的拉新数
            $sql_g_fan = "select id,openid,groups,sourceid,count(id) as g_laxin_count  from ims_water_salegroup3_fans where uniacid = neeedu  and themeid = needt  and  groups  in ($instr) and (sourceid is not null  and sourceid <> '') GROUP BY  groups  order by g_laxin_count DESC";
            $sql_g_fan = str_ireplace($needu, $uniacid, $sql_g_fan);
            $sql_g_fan = str_ireplace($needt, $themeid, $sql_g_fan);
            $sql_g_fan_sqldata = \think\facade\Db::query($sql_g_fan);
            //$arr['sql_fan_sqldata'] = $sql_g_fan_sqldata;
            $length_g_fan = count($sql_g_fan_sqldata);
            //$arr['length_g_fan'] = $length_g_fan;

            for ($i = 0; $i < $group_length; $i++) {
                //团队直推订单
                for ($j = 0; $j < $length_g_zhi; $j++) {
                    if ($sqldata[$i]['groups'] == $sql_g_zhi_record_sqldata[$j]['groups']) {
                        $sqldata[$i]['zhi_g_record_num'] = $sql_g_zhi_record_sqldata[$j]['g_zhi_count'];
                    }
                }
                //团队溯源订单
                for ($j = 0; $j < $length_g_su; $j++) {
                    if ($sqldata[$i]['groups'] == $sql_g_su_record_sqldata[$j]['groups']) {
                        $sqldata[$i]['su_g_record_num'] = $sql_g_su_record_sqldata[$j]['g_su_count'];
                    }
                }
                //网页访客数，即拉新数
                for ($j = 0; $j < $length_g_fan; $j++) {
                    if ($sqldata[$i]['groups'] == $sql_g_fan_sqldata[$j]['groups']) {
                        $sqldata[$i]['group_fan_num'] = $sql_g_fan_sqldata[$j]['g_laxin_count'];
                    }
                }
            }

            //再次排序
            usort($sqldata, function ($a, $b) {
                if(isset($b['zhi_g_record_num']) && isset($a['zhi_g_record_num']) ){
                    return $b['zhi_g_record_num'] <=> $a['zhi_g_record_num']; // 使用 PHP 7 的太空船操作符来进行比较
                }
            });

            //团队数据也要处理下
            $qie = 2; //员工排行是取前3
            if ($countlength > $qie) {
                //想获取前2个为一组
                $topdata["one"] = array_slice($sqldata, 0, $qie);
                $topdata["two"] = array_slice($sqldata, $qie, count($sqldata) - $qie);
            } else {
                $topdata["one"] = $sqldata;
            }
            $arr['sqldata'] = $sqldata;
            $arr['topdata'] = $topdata;
            $isDebug = \think\facade\App::isDebug();
            if ($isDebug) {
                $arr['sqlaction1_groups'] = $actionsql;
                $arr['sqlaction2_groups'] = $sql_g_zhi_record;
                $arr['sqlaction3_groups'] = $sql_g_su_record;
                $arr['sqlaction4_groups'] = $sql_g_fan;
            }
        } else {
            $arr['vdata'] = null;
        }
        return $arr;
    }

    //redtop
    public function redtop($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,uniacid,themeid,openid,`status`,nickname,avatar,redpackage from ims_water_salegroup3_fans where uniacid = neeedu  and themeid = needt and  `status`= 1 and (redpackage is not null and redpackage <> 0.00) ORDER BY redpackage DESC ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $lenth_sqldata = count($sqldata);
        $arr['lenth_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            $arr['sqldata'] = $sqldata;
        } else {
            $arr['vdata'] = null;
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        return $arr;
    }
    //testime
    public function testime($str){
        $startTime = microtime(true);
        $sqldata = \think\facade\Db::query($str);
        $endTime = microtime(true);
        $arr['m-time-1'] = $endTime - $startTime;
        return  $arr;
    }

    //yuyinone
    public function yuyinone($uniacid, $themeid,$recordid){
        $needu = 'neeedu';
        $needt = 'needt';
        $needr = 'needr';
        $orginsql = "select r.id,r.openid,r.fopenid,r.sourceid,r.groups,r.sourceid,w.username ,r.username as guanlian_username  from ims_water_salegroup3_record r
    left join
    ims_water_salegroup3_worker w on r.sourceid = w.openid  where r.uniacid = neeedu and  r.themeid = needt and r.id = needr  group by r.id  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($needt, $themeid, $orginsql);
        $actionsql = str_ireplace($needr, $recordid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $lenth_sqldata = count($sqldata);
        $arr['lenth_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            $arr['sqldata'] = $sqldata;
        } else {
            $arr['vdata'] = null;
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        $arr['x']='xx';
        return $arr;
    }

    //语音播放抓最新的10个订单 tentopOrder
    public function tentopOrder($uniacid, $themeid){
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select r.id,r.openid,r.fopenid,r.sourceid,r.groups,r.sourceid,w.username,r.username as guanlian_username  from ims_water_salegroup3_record r
    left join
    ims_water_salegroup3_worker w on r.sourceid = w.openid  where r.uniacid = neeedu and  r.themeid = needt and (r.`status` = 1  ) group by r.id order by r.addtime desc  limit 20 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $lenth_sqldata = count($sqldata);
        $arr['lenth_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            $arr['sqldata'] = $sqldata;
        } else {
            $arr['vdata'] = null;
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        $arr['x']='xx';
        return $arr;
    }

    //findbysn
    public function findbysn($uniacid, $themeid,$sn){
        $needu = 'neeedu';
        $needt = 'needt';

        $orginsql ="select * from ims_water_salegroup3_order where ordersn = $sn  ";

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;
    }

    public function findbyrr($uniacid, $themeid,$rr){
        $needu = 'neeedu';
        $needt = 'needt';

        $orginsql ="select * from ims_water_salegroup3_record where id  = $rr  ";

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;
    }

    //findOrder
    public function findOrder($uniacid, $themeid,$openid){
        $needu = 'neeedu';
        $needt = 'needt';
        $instr = "";
        $instr .= "'" .$openid. "'" . ',';
        $instr = substr($instr, 0, -1);

        $orginsql ="select * from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt  and fopenid in ( $instr ) ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($needt, $themeid, $orginsql);

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;

    }

    //eventslist 18事件大屏
    public function eventslist($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $limit_sql = 60;
        $orginsql = "select r.id,r.uniacid,r.themeid,r.`status`,r.avatar,r.nickname,r.way,r.username as guanlian_username,r.sourceid as rsourceid,r.addtime,w.username as laiyuan_username from ims_water_salegroup3_record r left join ims_water_salegroup3_worker w on r.sourceid = w.openid  where r.uniacid = neeedu  and r.themeid = needt and  (r.`status` = 1 or r.`status` = 0 ) group by r.id order by r.addtime desc ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $actionsql .= " limit " . $limit_sql;
        $startTime = microtime(true);
        $sqldata = \think\facade\Db::query($actionsql);
        $endTime = microtime(true);
        $arr['m-time-1'] = $endTime - $startTime;
        $arr['原来长度'] = count($sqldata);
        $lenth_sqldata = count($sqldata);
        $arr['length_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            //对事件名称遍历修改 例如 status = 0 事件名称为 "等待支付"  event_name
            //额外加一遍历,时间转换 addtime_text;
            foreach ($sqldata as &$recorditem) {
                if ($recorditem['addtime'] == 0) {
                    $recorditem['addtime_text'] = "--";
                } else {
                    $recorditem['addtime_text'] = date('m-d H:i:s', $recorditem['addtime']);
                }
                switch ($recorditem['status']) {
                    case 0:
                        $recorditem['event_name'] = '等待支付';
                        break;
                    case 1:
                        $recorditem['event_name'] = '支付成功';
                        break;
                    default:
                        $recorditem['event_name'] = '等待处理';
                }
                unset($recorditem);
            }

            $arr['sqldata'] = $sqldata;
            $sql_comm = " select  c.id,c.uniacid,c.themeid,c.`status`,c.avatar,c.nickname,c.fee,c.type,c.ordersn,c.addtime,o.username as  guanlian_username, f.province,w.username as laiyuan_username from ims_water_salegroup3_comm c
            left join ims_water_salegroup3_fans f on c.openid = f.sourceid  
            left join ims_water_salegroup3_worker w on w.openid = f.sourceid
            left join ims_water_salegroup3_order o on c.ordersn = o.ordersn
            where c.uniacid = neeedu and c.themeid = needt   and c.`status` = 1 group by id order by c.addtime desc ";

            $sql_comm = "	SELECT 
    sq.id,sq.uniacid,sq.openid,sq.themeid,sq.`status`,sq.avatar,sq.nickname,sq.fee,sq.type,sq.ordersn,sq.addtime, sq.province,sq.sourceid,sq.ordersn,w.username as laiyuan_username,o.ordersn as oordersn,o.nickname as guanlian_username
FROM (
    select   c.id,c.uniacid,c.openid,c.themeid,c.`status`,c.avatar,c.nickname,c.fee,c.type,c.ordersn,c.addtime, f.province,f.sourceid from ims_water_salegroup3_comm c            join ims_water_salegroup3_fans f on c.openid = f.sourceid  
	 where c.uniacid = neeedu and c.themeid = needt   and c.`status` = 1  GROUP BY c.id ORDER BY c.addtime desc limit 60	
) AS sq
Left JOIN  ims_water_salegroup3_worker w on w.openid = sq.sourceid   
left join ims_water_salegroup3_order o on sq.ordersn = o.ordersn  GROUP BY sq.id  ";

            $sql_comm = str_ireplace($needu, $uniacid, $sql_comm);
            $sql_comm = str_ireplace($needt, $themeid, $sql_comm);

            //$sql_len_conm = "SELECT COUNT(id) as sumlen FROM (" . $sql_comm . ")  AS temp_record_view";
            $startTime = microtime(true);
            //没用到就不查  $sql_len_sqldata = \think\facade\Db::query($sql_len_conm);
            $endTime = microtime(true);
            $arr['m-time-2'] = $endTime - $startTime;


            $comm_limmit = $lenth_sqldata;
            $sql_comm .= "limit " . $comm_limmit;
            $startTime = microtime(true);
            $sql_comm_sqldata = \think\facade\Db::query($sql_comm);
            $endTime = microtime(true);
            $arr['m-time-3'] = $endTime - $startTime;
            $arr['sql_comm_sqldata'] = $sql_comm_sqldata;
            $length_comm = count($sql_comm_sqldata);
            $arr['length_comm'] = $length_comm;
            if ($length_comm > 0) {
                foreach ($sql_comm_sqldata as &$item) {  // 使用引用符号 & 以便直接修改原始数组中的对象
                    // 根据 type 的值设置状态
                    if ($item['addtime'] == 0) {
                        $item['addtime_text'] = "---";
                    } else {

                        $item['addtime_text'] = date('m-d H:i:s', $item['addtime']);

                    }

                    if ($item['fee'] > 0) {
                        $item['event_name'] = '获得' . $item['fee'] . '元红包';
                    }

                    switch ($item['type']) {
                        case 0:
                            $item['way'] = '一级开团';
                            break;
                        case 1:
                            $item['way'] = '一级参团';
                            break;
                        case 2:
                            $item['way'] = '二级推荐';
                            break;
                        case 3:
                            $item['way'] = '分享红包';
                            break;
                        case 4:
                            $item['way'] = '拉新红包';
                            break;
                        case 5:
                            $item['way'] = '团长红包';
                            break;
                        case 6:
                            $item['way'] = '溯源红包';
                            break;
                        case 7:
                            $item['way'] = '团员红包';
                            break;
                        case 8:
                            $item['way'] = '累计红包';
                            break;
                        case 9:
                            $item['way'] = '未知状态1';
                            break;
                        case 10:
                            $item['way'] = '未知状态2';
                            break;
                        case 11:
                            $item['way'] = '未知状态3';
                            break;
                        case 12:
                            $item['way'] = '满团红包';
                            break;
                        case 13:
                            $item['way'] = '浏览红包';
                            break;
                        case 14:
                            $item['way'] = '自购红包';
                            break;
                        case 15:
                            $item['way'] = '梯次红包';
                            break;
                        default:
                            $item['way'] = '未知状态';
                    }

                }
                $combinedArray = array_merge($sqldata, $sql_comm_sqldata);
                usort($combinedArray, function ($a, $b) {
                    return $b['addtime'] <=> $a['addtime']; // 使用 PHP 7 的太空船操作符来进行比较
                });
                unset($item);
                //$arr['sort_data'] = $combinedArray;  //
                $arr['现在长度'] = count($combinedArray);
                $arr['sqldata'] = $combinedArray;
            }


        } else {
            $arr['vdata'] = null;
            $sql_comm = '没必要要查红包了';
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
            $arr['actionsql2'] = $sql_comm;
        }
        $arr['isDebug'] = $isDebug;
        //$arr['ask表'] = "ims_water_salegroup3_record 和 ims_water_salegroup3_worker";
        return $arr;
    }

}
